﻿//=============================================================
// Copyright (C) 2015 福流网络
// 作 者：江怀玉
// 日 期：2015-4-23
// 描 述: 用于生成数据结果的JSON数据格式
// URL parameter:
//        entity:当前实体对象名称(不一定是表名)
//        codition:过滤数据条件
//        pageindex:当前页
//        pagesize:页记录数
//        type:JSON的数据类型，有时候用于List.aspx使用，有时用于生成对应的JS Object Entity!
//        order:排序
// 版 本：1.00
// 修改历史纪录
// 版 本  修改时间      修改人            修改内容
//=============================================================
using System;
using System.Collections.Generic;
using System.Web;
using System.Linq;
using FL.Common;
using FL.DBModule;
using System.IO;
using System.Text;
using FL.Bussiness;

namespace FL.Web.BackEnd
{
    public partial class DataTableJSONList : BasePage
    {

        protected void Page_Load(object sender, EventArgs e)
        {

            string action = GetStringFromQueryString("action", "");
            action = action == "" ? this.Request["action"] : action;
            switch (action)
            {
                case "sql":
                    getData();
                    break;
                case "getInstance":
                    getInstance();
                    break;
                default:
                    getData();
                    break;
            }
        }


        /// <summary>
        /// 获取实体
        /// </summary>
        private void getInstance()
        {
            string strEntity = this.GetStringFromQueryString("entity", "DatUser");
            DataTableBaseEntity entity = new DataTableBaseEntity();
            entity.TableName = strEntity;
            entity.CreateDT();
            entity.DRow[entity.getKey()] = Guid.NewGuid();
            entity.EntityJson = FL.Common.Globals.DataRowToJSON(((DataTableBaseEntity)entity).DRow);
            HttpContext.Current.Response.Write(entity.EntityJson.Replace("\\\\\"", "\\\"").Replace("\\", "\\\\"));
        }

        private void getData()
        {
            //if (this.UserId == null)
            //{
            //    Response.Write("[]");
            //    return;
            //}

            string strEntity = this.GetStringFromQueryString("entity", "");            //对应的实体
            if(string.IsNullOrEmpty(strEntity))
            {
                HttpContext.Current.Response.Write("{}");
                HttpContext.Current.Response.End();
                return;
            }
            string strCondition = this.GetStringFromQueryString("condition", "");            //系统特定条件
            string strCustomCondition = this.GetStringFromQueryString("customCondition", "");//自定义条件
            int iPageIndex = this.GetIntFromQueryString("pageindex", 0);                     //第N页？
            int iPageSize = this.GetIntFromQueryString("pagesize", 0);                       //页记录数？
            string IsOnlyTotal = this.GetStringFromQueryString("onlyTotal", "false");         //是否只回传记录总数?
            string IsDesc = this.GetStringFromQueryString("desc", "Yes");                  //Yes or No
            string iDataGridData = this.GetStringFromQueryString("datagrid", "false");
            string filter = this.GetStringFromQueryString("field", "");
            if (this.Request["pageIndex"] != null)
            {
                iPageIndex = iPageIndex == 0 ? int.Parse(this.Request["pageIndex"]) : iPageIndex;
            }
            if (iDataGridData == "true")
            {
                iPageIndex = iPageIndex == 0 ? 1 : iPageIndex + 1;
            }

            if (this.Request["pageSize"] != null)
            {
                iPageSize = iPageSize == 0 ? int.Parse(this.Request["pageSize"]) : iPageSize;
            }
            if (iPageSize == 0)
            {
                iPageSize = 20;
            }


            if (this.Request.Form["condition"] != null)
            {
                strCondition = strCondition == "" ? this.Request["condition"].ToString() : strCondition + " and " + this.Request["condition"].ToString();
            }

            //需要数据权限限制的情况
            if (GetStringFromQueryString("nodataright", "false") != "true")
            {
                string dataRightCondition = GetUserFunctionDataRightCondition();
                strCondition = strCondition == "" ? dataRightCondition : strCondition + (dataRightCondition == "" ? "" : " and (" + dataRightCondition + ")");
                //strCondition = strCondition == "" ? strCustomCondition : strCondition + (strCustomCondition == "" ? "" : " and " + strCustomCondition);
            }

            strCustomCondition = strCustomCondition
                     .Replace("@UserID", this.UserId.ToString())
                     .Replace("@FunctionID",this.FunctionID.ToString())
                     .Replace("@UserName", this.UserName)
                     .Replace("@channel_id", this.GetStringFromQueryString("channel_id", Guid.Empty.ToString()));

            strCondition = strCondition
                                 .Replace("@UserID", this.UserId.ToString())
                                 .Replace("@FunctionID", this.FunctionID.ToString())
                                 .Replace("@UserName", this.UserName);


            strCondition = strCondition.Replace("'%20", "@#$%^&*").Replace("%20", " ").Replace("@#$%^&*", "'%20");
            if (!string.IsNullOrEmpty(strCustomCondition))
            {
                strCondition = "(" + (string.IsNullOrEmpty(strCondition) ? "1=1" : strCondition) + ") and (" + strCustomCondition + ")";
            }

            // 用于autocomplate控件获取数据源使用
            if (filter != "")
            {
                string[] _filters = filter.Split(',');
                if (Request["key"] != null)
                {
                    string _strCondition = "";
                    foreach (string _filter in _filters)
                    {
                        if (_strCondition == "")
                        {
                            _strCondition = _filter + string.Format(" like '%{0}%'", Request["key"].ToString().TrimStart().TrimEnd());
                        }
                        else
                        {
                            _strCondition = _strCondition + " or " + _filter + string.Format(" like '%{0}%'", Request["key"].ToString().TrimStart().TrimEnd());
                        }
                    }
                    _strCondition = "(" + _strCondition + ")";
                    strCondition = _strCondition;
                }
            }

            string strType = this.GetStringFromQueryString("type", "");
            string strOrder = this.GetStringFromQueryString("order", "");
            strEntity = strEntity.Split(',')[0];
            //排序
            String sortField = Request["sortField"];
            string sortOrder = Request["sortOrder"];
            if (!string.IsNullOrEmpty(sortField))
            {
                strOrder = sortField;
            }
            if (!string.IsNullOrEmpty(sortOrder))
            {
                IsDesc = sortOrder == "desc" ? "Yes" : "No";
            }
            BaseEntity entity = FL.Common.Reflection.CreateEntitysInstance(strEntity) as BaseEntity;

            if (entity == null)
            {
                entity = new DataTableBaseEntity();
                var _entity = entity as DataTableBaseEntity;
                //_entity.TableKey = _entity.GetTableId();
                _entity.TableName = strEntity;
            }
            else
            {
                strEntity = entity.GetTableName();
                entity = new DataTableBaseEntity();
                (entity as DataTableBaseEntity).TableName = strEntity;
            }

            BaseBussnies<BaseEntity> bll = new BaseBussnies<BaseEntity>(entity);
            iPageSize = iPageSize == 0 ? 25 : iPageSize;
            bll.PageIndex = iPageIndex;
            bll.PageSize = iPageSize;
            bll.Order = strOrder;
            bll.Desc = IsDesc;//"Yes";//2012.6.18改
            System.Data.DataTable list;
            int totalrecord = 0;
            if (this.GetStringFromQueryString("action", "") == "sql")
            {
                string sql = getEntitySql();

                //string SqlComm = "SELECT  *  FROM (" + sql + ") as target_table" + (string.IsNullOrEmpty(strCondition) ? "" : " where " + strCondition);
                switch (strType)
                {
                    case "1":
                        list = bll.GetDataTableSplitPage(sql, strCondition);
                        string str2 = FL.Common.Globals.DataTableToJSON(list).Replace("\n", " ").Replace("\r", " ").Replace("null", "").Replace("[BR]", " ").Replace("@@@@", " ").Replace("@@", " ");
                        HttpContext.Current.Response.Write("" + str2.Replace("null", "").Replace("[A]", "&") + "");
                        break;
                    case "2":
                        break;
                    case "3":
                        bll.PageSize = 10000;
                        list = bll.GetDataTableSplitPage(sql, strCondition);
                        OutputExcel(list);
                        break;
                    default:
                        getSqlData(iPageSize, iPageIndex, strOrder, IsDesc, strCondition);
                        break;

                }
            }
            else
            {
                totalrecord = bll.GetRowCount(strCondition);
                if (IsOnlyTotal == "true")
                {
                    Response.Write("[{\"total\":" + totalrecord.ToString() + "}]");
                    return;
                }
                if (bll.PageIndex > 0)
                {
                    list = string.IsNullOrEmpty(strCondition) ? bll.GetDataTableSplitPage("1=1") : bll.GetDataTableSplitPage(strCondition);
                }
                else
                {
                    list = string.IsNullOrEmpty(strCondition) ? bll.GetDataTableByCondition("1=1") : bll.GetDataTableByCondition(strCondition);
                }
                if (list == null)
                {
                    HttpContext.Current.Response.Write("[{\"N\":\"null\"}]");
                    return;
                }

                string strFormat = "";
                string str = "";
                switch (strType)
                {
                    case "1":
                        string str2 = FL.Common.Globals.DataTableToJSON(list).Replace("\n", " ").Replace("\r", " ").Replace("null", "").Replace("[BR]", " ").Replace("@@@@", " ").Replace("@@", " ");
                        HttpContext.Current.Response.Write("" + str2.Replace("null", "").Replace("[A]", "&") + "");
                        break;
                    case "2":
                        strFormat = "{\"total\":" + totalrecord.ToString() + ",\"data\":";
                        str = FL.Common.Globals.DataTableToJSON(list).Replace("\n", " ").Replace("\r", " ").Replace("null", "").Replace("[BR]", " ").Replace("@@@@", " ").Replace("@@", " ");
                        strFormat = strFormat + (string.IsNullOrEmpty(str) ? "[]" : str) + "}";
                        HttpContext.Current.Response.Write("var _Data" + strEntity + " = " + strFormat + ";");
                        break;
                    case "3":
                        OutputExcel(list);
                        break;
                    default:
                        strFormat = "{\"total\":" + totalrecord.ToString() + ",\"data\":";
                        str = FL.Common.Globals.DataTableToJSON(list).Replace("\n", " ").Replace("\r", " ").Replace("null", "").Replace("[BR]", " ").Replace("@@@@", " ").Replace("@@", " ");
                        strFormat = strFormat + (string.IsNullOrEmpty(str) ? "[]" : str);
                        strFormat = strFormat + GetTotalData(GetStringFromQueryString("totalfields", ""), strEntity, strCondition);
                        strFormat = strFormat + "}";
                        HttpContext.Current.Response.Write(strFormat);
                        break;

                }
            }
        }


        /// <summary>
        /// 获取角色数据权限信息
        /// </summary>
        /// <returns></returns>
        public string GetUserFunctionDataRightCondition()
        {
            FL.Bussiness.Sys.BUser user = new Bussiness.Sys.BUser();
            return user.GetUserFunctionDataRightCondition(this.UserId, this.GetGuidFromQueryString("functionid"), GetStringFromQueryString("entity", ""));
            //return "";
        }

        #region 统计数据

        /// <summary>
        /// 获取统计数据
        /// </summary>
        /// <param name="fields">字段</param>
        /// <param name="table">数据表</param>
        /// <param name="condition">条件</param>
        /// <returns>JSON结果</returns>
        private string GetTotalData(string fields, string table, string condition)
        {
            if (string.IsNullOrEmpty(fields))
            {
                return ",totaldata:[{}]";
            }
            string[] _fields = fields.Split(',');
            StringBuilder str = new StringBuilder("count(*) as records");
            foreach (string field in _fields)
            {
                if (str.ToString() == "")
                {
                    str.Append("max(" + field + ") as max_" + field);
                }
                else
                {
                    str.Append(",max(" + field + ") as max_" + field);
                }
                str.Append(",min(" + field + ") as min_" + field);
                str.Append(",avg(" + field + ") as avg_" + field);
                str.Append(",sum(" + field + ") as sum_" + field);
            }
            string sql = "select " + str.ToString() + " from " + table + " where (1=1) " + (string.IsNullOrEmpty(condition) ? "" : " and " + condition);
            System.Data.DataTable dt = _bll.GetDataBySqlComm(sql);
            if (dt == null || dt.Rows.Count == 0)
            {
                return ",totaldata:[{}]";
            }
            return ",totaldata:" + FL.Common.Globals.DataTableToJSON(dt);
        }


        /// <summary>
        /// 获取统计数据
        /// </summary>
        /// <param name="fields">字段</param>
        /// <param name="table">数据表</param>
        /// <param name="condition">条件</param>
        /// <returns>JSON结果</returns>
        private string GetTotalData(string fields, string sql_table, string condition, bool is_sql)
        {
            string table = sql_table;
            if (is_sql)
            {
                table = "(" + sql_table + ") as targeTable";
            }
            return GetTotalData(fields, table, condition);
        }

        #endregion


        #region getSqlData
        /// <summary>
        /// 获取Sql数据
        /// </summary>
        private void getSqlData(int pagesize, int pageindex, string strOrder, string isDesc, string condition)
        {
            //iPageSize = iPageSize == 0 ? 25 : iPageSize;
            this._bll.PageIndex = pageindex;
            this._bll.PageSize = pagesize;
            this._bll.Order = strOrder;
            this._bll.Desc = isDesc;//"Yes";//2012.6.18改

            string strEntity = this.GetStringFromQueryString("entity", "DatUser");       //对应的实体
            string strCode = this.GetStringFromQueryString("code", "");                  //系统特定条件
            string sql = getEntitySql(strEntity, strCode);
            //IList<FL.DBModule.Sys.t_sys_datagrid_sql> list = this._bll.GetEntitys<FL.DBModule.Sys.t_sys_datagrid_sql>(string.Format("entity='{0}' and code='{1}'", strEntity, strCode));
            if (string.IsNullOrEmpty(sql))
            {
                if (Request.Form["sql"] == "")
                {
                    Response.Write("{rows:0,data:[]}");
                    Response.End();
                }
                else
                {
                    getSqlData(Request.Form["sql"], condition);
                }
            }
            else
            {
                getSqlData(sql, condition);
            }
        }


        private string getEntitySql()
        {
            string strEntity = this.GetStringFromQueryString("entity", "DatUser");       //对应的实体
            string strCode = this.GetStringFromQueryString("code", "");                  //系统特定条件
            return getEntitySql(strEntity, strCode);
        }


        private string getEntitySql(string strEntity, string strCode)
        {
            string sql = "";
            IList<FL.DBModule.Sys.t_sys_datagrid_sql> list =(new FL.Bussiness.BaseBussnies()).GetEntitys<FL.DBModule.Sys.t_sys_datagrid_sql>(string.Format("entity='{0}' and code='{1}'", strEntity, strCode));
            if (list != null)   //|| list.Count > 0
            {
                if (list.Count > 0)
                {
                    sql = list[0].select_sql;
                    sql = sql.Replace("@UserID", this.UserId.ToString())
                        .Replace("@UserName", this.UserName);
                    sql = sql.Replace("@FunctionID", this.FunctionID.ToString());

                    foreach (string key in Request.Form.Keys)
                    {
                        sql = sql.Replace("@" + key, Request.Form[key]);
                    }
                    foreach (string key in Request.QueryString.Keys)
                    {
                        sql = sql.Replace("@" + key, Request.QueryString[key]);
                    }
                    sql = sql.Replace("@beginDate", "1900-01-01");
                    sql = sql.Replace("@endDate", "2050-12-31");
                    /*-------*/
                    sql = sql.Replace("@deadDate", DateTime.Now.ToString("yyyy-MM-dd"));
                    sql = sql.Replace("@fin_aging_statistics_status", "1,2,3");
                    /*-------*/
                    /*-------*///getSqlData(sql, condition);
                }
            }
            return sql;
        }


        private void getSqlData(string sql, string condition)
        {
            ////不允许新增修改删除SQL
            if (sql.Contains("insert") || sql.Contains("delete") || sql.Contains("drop"))
            {
                Response.Write("{rows:0,data:[]}");
                Response.End();
                return;
            }

            int totalrecord = this._bll.GetRowCount(sql, condition);
            System.Data.DataTable list = this._bll.GetDataTableSplitPage(sql, condition);
            OutputJson(totalrecord, list, sql, condition);
        }
        #endregion

        #region 导出
        /// <summary>
        /// 生成要导出的Execl数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string CreateHTML(System.Data.DataTable dt)
        {

            IList<FL.DBModule.Sys.t_set_datagrid> datagrid = _bll.GetEntitys<FL.DBModule.Sys.t_set_datagrid>("entity='" + GetStringFromQueryString("entity", "") + "' and isnull(is_output,'')<> 'N'");
            StringBuilder execlHTML = new StringBuilder("<table><tr><td>序号</td>");
            if (datagrid == null)
            {
                return "";
            }
            IList<string> _cols = new List<string>();
            //foreach (System.Data.DataColumn column in dt.Columns)
            //{


            IList<FL.DBModule.Sys.t_set_datagrid> _list = datagrid.OrderBy(p => p.sort).ToList();//datagrid.Where(p => p.name == column.ColumnName).ToList();

            foreach (FL.DBModule.Sys.t_set_datagrid _datagrid in _list)
            {
                try
                {
                    if (_datagrid.is_visiable == "N")
                    {
                        continue;
                    }
                    int index = dt.Columns[_datagrid.name].Ordinal;
                    execlHTML.Append(string.Format("<td style='mso-number-format:\"\\@\";'>{0}</td>", _datagrid.caption));
                }
                catch (Exception ex) { };
            }

            execlHTML.Append("</tr>");
            {
                int rowIndex = 0;
                foreach (System.Data.DataRow row in dt.Rows)
                {

                    rowIndex = rowIndex + 1;
                    execlHTML.Append("<tr><td>" + rowIndex.ToString() + "</td>");

                    IList<FL.DBModule.Sys.t_set_datagrid> list = datagrid.OrderBy(p => p.sort).ToList();

                    foreach (FL.DBModule.Sys.t_set_datagrid _datagrid in list)
                    {
                        try
                        {

                            if (_datagrid.is_visiable == "N")
                            {
                                continue;
                            }
                            int index = dt.Columns[_datagrid.name].Ordinal;
                            if (_datagrid.data_type.ToString().IndexOf("char") > 0)
                            {
                                execlHTML.Append(string.Format("<td style='mso-number-format:\"\\@\";'>{0}</td>", row.ItemArray[index]));
                            }
                            else
                            {
                                execlHTML.Append(string.Format("<td>{0}</td>", row.ItemArray[index]));
                            }
                        }
                        catch (Exception ex) { };

                    }
                    execlHTML.Append("</tr>");
                }
                execlHTML.Append("</table>");
                return execlHTML.ToString();
            }
        }


        public void OutputJson(int totalrecord, System.Data.DataTable list, string strEntity, string strCondition)
        {
            string strFormat = "{\"total\":" + totalrecord.ToString() + ",\"data\":";
            string str = FL.Common.Globals.DataTableToJSON(list).Replace("\n", " ").Replace("\r", " ").Replace("null", "").Replace("[BR]", " ").Replace("@@@@", " ").Replace("@@", " ");
            strFormat = strFormat + (string.IsNullOrEmpty(str) ? "[]" : str);
            strFormat = strFormat + GetTotalData(GetStringFromQueryString("totalfields", ""), strEntity, strCondition, true);
            strFormat = strFormat + "}";
            HttpContext.Current.Response.Write(strFormat);
            Response.End();
        }




        private void OutputExcel(System.Data.DataTable list)
        {
            Response.Clear();
            Response.Write(CreateHTML(list));
            Response.Buffer = true;
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddssmm") + ".xls");
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            this.EnableViewState = false;
        }


        public void NpoiExcel(System.Data.DataTable dt, string title)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");


            IList<FL.DBModule.Sys.t_set_datagrid> datagrid = _bll.GetEntitys<FL.DBModule.Sys.t_set_datagrid>("entity='" + GetStringFromQueryString("entity", "") + "' and isnull(is_output,'')<> 'N'");
            if (datagrid != null)
            {
                foreach (FL.DBModule.Sys.t_set_datagrid entity in datagrid)
                {
                    dt.Columns[entity.name].ColumnName = entity.caption;
                }
            }
            int rows = 0;
            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(rows);
            NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (datagrid.Where(p => (p.caption == dt.Columns[i].ColumnName || p.name == dt.Columns[i].ColumnName) && p.is_visiable != "N" && p.is_output != "N").Count() > 0)
                {
                    NPOI.SS.UserModel.ICell cell = headerrow.CreateCell(i);
                    cell.CellStyle = style;
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }
            }

            foreach (System.Data.DataRow row in dt.Rows)
            {
                rows++;
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rows);
                NPOI.SS.UserModel.ICellStyle dataRowStyle = book.CreateCellStyle();
                dataRowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                dataRowStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    if (datagrid.Where(p => (p.caption == dt.Columns[i].ColumnName || p.name == dt.Columns[i].ColumnName) && p.is_visiable != "N" && p.is_output != "N").Count() > 0)
                    {
                        string dataType = dt.Columns[i].DataType.ToString();
                        NPOI.SS.UserModel.ICell dataCell = dataRow.CreateCell(i);
                        if (dataType.Contains("Decimal") || dataType.Contains("Int") || dataType.Contains("Double"))
                        {
                            dataCell.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
                        }

                        dataCell.CellStyle = dataRowStyle;
                        dataCell.SetCellValue(row.ItemArray[dt.Columns[i].Ordinal].ToString());
                    }
                }
                //execlHTML.Append("<tr>");
                //foreach (System.Data.DataColumn column in dt.Columns)
                //{
                //    execlHTML.Append(string.Format("<td>{0}</td>", row.ItemArray[column.Ordinal]));
                //}
                //execlHTML.Append("</tr>");
            }


            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyyMMddmmHHss"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
        #endregion
    }
}
